
package com.feib.soeasy.dao.jdbc;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.Soez5005Dao;
import com.feib.soeasy.model.WriteOffPayment;


/**
 * @title (#)Soez5005JdbcDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Repository("soez5005Dao")
public class Soez5005JdbcDao extends JdbcDaoSupport implements Soez5005Dao {
    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    
    @Resource(name = "dataSource")
    public void setMyDataSource(DataSource dataSource) {
        super.setDataSource(dataSource); 
    }
    
    /**
     * 超商統計資料
     * @param groupPk 帳單業者
     * @param channel 通路, D:7-11, E:OK, F:全家, G:萊爾富, NULL: 4家超商
     * @param qryDateType 資料期間基礎：1: 實際入帳日  2:繳款交易日。
     * @param qryDateFrom 資料期間起
     * @param qryDateTo 資料期間迄
     * @return 
     */
    public List<Map<String, Object>> getStatisticsForGroup(Long groupPk, String channel, Integer qryDateType, Date qryDateFrom, Date qryDateTo) 
    {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        
        sql.append("SELECT ").append("\r\n");
        sql.append(" GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO").append("\r\n");
        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT ").append("\r\n");
        /*  電金部2011/05/09報表邏輯異動
        1.手續費負擔別為"內含"時,"業者負擔"="超商成本"
        2.手續費負擔別為"外加"時,  "業者負擔"+  "繳費者負擔"  ="超商成本"
                    銀行端實際撥款金額(內含) =>  代收金額  -  "業者負擔"
                    銀行端實際撥款金額(外加) =>  代收金額   -  "業者負擔"
                    客戶端端實際撥款金額(內含) =>   代收金額-   代收手續費 ( "業者負擔"  )   -帳務處理費
                    客戶端實際撥款金額(外加) =>     代收金額-    代收手續費 ( "業者負擔"  ) -帳務處理費
       */
        //2011/05/13 jeff Gu  start   修正    客戶端端實際撥款金額(內含) =>   代收金額-   代收手續費 ( "業者負擔"  )   -帳務處理費
        sql.append(", SUM(AMOUNT - (GROUP_AMOUNT) - BANK_AMOUNT ) AS REAL_AMOUNT ").append("\r\n");
        //2011/05/13 jeff Gu  end       
        sql.append("FROM ").append("\r\n");
        sql.append("( ").append("\r\n");
        sql.append("SELECT TMP.* ").append("\r\n");
        sql.append(", C.COMMISSION_CODE_PK ").append("\r\n");
        sql.append(", C.COMMISSION_CODE_NAME ").append("\r\n");
        sql.append(", C.COMMISSION_TYPE ").append("\r\n");
        sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ").append("\r\n"); //超商成本
        sql.append(", ISNULL(C.BANK_AMOUNT, 0 ) AS BANK_AMOUNT ").append("\r\n"); //銀行帳務處理費
        sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ").append("\r\n"); //繳款者負擔
        sql.append(", ISNULL(GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ").append("\r\n"); //業者負擔
        sql.append("FROM ").append("\r\n");
        sql.append("( ").append("\r\n");
        sql.append("select W.WRITE_OFF_PAYMENT_PK ").append("\r\n");
        sql.append(", P.PAYMENT_PK ").append("\r\n");
        sql.append(", P.GROUP_PK ").append("\r\n");
        sql.append(", U.FORMAT_DEF_PK ").append("\r\n");
        sql.append(", W.AMOUNT ").append("\r\n");
        sql.append(", W.WRITE_OFF_DATE ").append("\r\n");
        sql.append(", W.CREATED_DATE ").append("\r\n");
        sql.append(", P.BARCODE_STORES_1 ").append("\r\n");
        sql.append(", P.TOTAL_AMOUNT ").append("\r\n");
        sql.append(", P.TOTAL_LOANABLE_AMOUNT ").append("\r\n");
        sql.append(", W.CHANNEL ").append("\r\n");
        sql.append(", RA.VIRTUAL_ACCOUNT_NO, RA.ACCOUNT_NO, AP.GROUP_NAME").append("\r\n");
        sql.append(", CASE  W.AMOUNT WHEN P.TOTAL_AMOUNT THEN SUBSTRING( P.BARCODE_STORES_1, 7, 9) ELSE  SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) END AS COMMISSION_CODE ").append("\r\n");
        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ").append("\r\n");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ").append("\r\n");
        sql.append(" INNER JOIN APP_GROUP AP ON AP.GROUP_PK=P.GROUP_PK").append("\r\n");
        sql.append(" LEFT  JOIN REAL_ACCOUNT RA ON left(RTRIM(P.VIRTUAL_ACCOUNT),LEN(RA.VIRTUAL_ACCOUNT_NO)) = RA.VIRTUAL_ACCOUNT_NO  and ra.GROUP_PK=p.GROUP_PK").append("\r\n");//黃郁珊 依問題單修改問題
        sql.append(" where P.PAYMENT_STATUS = 5 ").append("\r\n"); //已繳款
        sql.append(" AND P.GROUP_PK = ? ").append("\r\n"); // 帳單業者
        args.add(groupPk);

        if (null != channel)
        {
            sql.append(" AND W.CHANNEL = ? ").append("\r\n"); // 通路
            args.add(channel);
        }
        else
        {
            sql.append(" AND W.CHANNEL in( ?, ?, ?, ?) ").append("\r\n"); // 通路
            args.add(WriteOffPayment.CHANNEL_7_11);
            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
            args.add(WriteOffPayment.CHANNEL_HILIFE);
            args.add(WriteOffPayment.CHANNEL_OK_MART);
        }

        // 資料期間 1: 銷帳日期  2:實際繳款日
        if (new Integer(1).equals(qryDateType))
            sql.append(" AND W.WRITE_OFF_DATE between ? and ? ").append("\r\n"); // 銷帳日期
        else
            sql.append(" AND W.TX_DATE between ? and ? ").append("\r\n"); // 實際繳款日

        args.add(qryDateFrom);
        args.add(qryDateTo);

        sql.append(") TMP ").append("\r\n");
        sql.append(" ").append("\r\n");
        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ").append("\r\n");
        sql.append("INNER  JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ").append("\r\n");
        sql.append(") D ").append("\r\n");
        sql.append("GROUP BY GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO ").append("\r\n");
        sql.append("ORDER BY GROUP_NAME, VIRTUAL_ACCOUNT_NO ").append("\r\n");
        
        logger.debug("Soez5005JdbcDao.getStatisticsForGroup SQL=[{}], args={}", sql, args);


        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());

    }
    
/*
 * 2011.04.25 教育訓練時提出，
 * 此表只算遠銀要給超商多少錢，
 * 為了怕反悔，還是先保留原本的code    
 */
//    /**
//     * 超商統計資料
//     * @param groupPk 帳單業者
//     * @param channel 通路, D:7-11, E:OK, F:全家, G:萊爾富, NULL: 4家超商
//     * @param qryDateType 資料期間基礎：1: 實際入帳日  2:繳款交易日。
//     * @param qryDateFrom 資料期間起
//     * @param qryDateTo 資料期間迄
//     * @return 
//     */
//    public List<Map<String, Object>> getStatisticsForGroup(Long groupPk, String channel, Integer qryDateType, Date qryDateFrom, Date qryDateTo) 
//    {
//        StringBuffer sql = new StringBuffer();
//        List<Object> args = new ArrayList<Object>();
//
//        sql.append("SELECT ").append("\r\n");
//        sql.append(" GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO").append("\r\n");
//        sql.append(", SUM(RECORD_COUNT) AS RECORD_COUNT ").append("\r\n");
//        sql.append(", SUM(REAL_AMOUNT) AS REAL_AMOUNT ").append("\r\n");
//        sql.append(" from ( ");
//        
//        sql.append("SELECT ").append("\r\n");
//        sql.append(" GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO").append("\r\n");
//        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT ").append("\r\n");
//        // C:超商成本  D:帳單業者負擔    E:繳款者負擔  F:本行帳務處理費 
//        //實際入帳金額小計 = 代收金額小計(繳費單上的繳費金額*筆數)  - 代收手續費小計(筆數* (C-E +D)) - 帳務處理費小計(筆數*F)   
//        sql.append(", SUM(AMOUNT - (COMMISSION_AMOUNT - PAYER_AMOUNT + GROUP_AMOUNT) - BANK_AMOUNT ) AS REAL_AMOUNT ").append("\r\n");
//        sql.append("FROM ").append("\r\n");
//        sql.append("( ").append("\r\n");
//        sql.append("SELECT TMP.* ").append("\r\n");
//        sql.append(", C.COMMISSION_CODE_PK ").append("\r\n");
//        sql.append(", C.COMMISSION_CODE_NAME ").append("\r\n");
//        sql.append(", C.COMMISSION_TYPE ").append("\r\n");
//        sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ").append("\r\n"); //超商成本
//        sql.append(", ISNULL(C.BANK_AMOUNT, 0 ) AS BANK_AMOUNT ").append("\r\n"); //銀行帳務處理費
//        sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ").append("\r\n"); //繳款者負擔
//        sql.append(", ISNULL(GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ").append("\r\n"); //業者負擔
//        sql.append("FROM ").append("\r\n");
//        sql.append("( ").append("\r\n");
//        sql.append("select W.WRITE_OFF_PAYMENT_PK ").append("\r\n");
//        sql.append(", P.PAYMENT_PK ").append("\r\n");
//        sql.append(", P.GROUP_PK ").append("\r\n");
//        sql.append(", U.FORMAT_DEF_PK ").append("\r\n");
//        sql.append(", W.AMOUNT ").append("\r\n");
//        sql.append(", W.WRITE_OFF_DATE ").append("\r\n");
//        sql.append(", W.CREATED_DATE ").append("\r\n");
//        sql.append(", P.BARCODE_STORES_1 ").append("\r\n");
//        sql.append(", P.TOTAL_AMOUNT ").append("\r\n");
//        sql.append(", P.TOTAL_LOANABLE_AMOUNT ").append("\r\n");
//        sql.append(", W.CHANNEL ").append("\r\n");
//        sql.append(", RA.VIRTUAL_ACCOUNT_NO, RA.ACCOUNT_NO, AP.GROUP_NAME").append("\r\n");
//        sql.append(", CASE  W.AMOUNT WHEN P.TOTAL_AMOUNT THEN SUBSTRING( P.BARCODE_STORES_1, 7, 9) ELSE  SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) END AS COMMISSION_CODE ").append("\r\n");
//        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ").append("\r\n");
//        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ").append("\r\n");
//        sql.append(" INNER JOIN APP_GROUP AP ON AP.GROUP_PK=P.GROUP_PK").append("\r\n");
//        sql.append(" LEFT  JOIN REAL_ACCOUNT RA ON RA.VIRTUAL_ACCOUNT_NO=SUBSTRING( P.VIRTUAL_ACCOUNT, 1, 4) and ra.GROUP_PK=p.GROUP_PK").append("\r\n");
//        sql.append(" where P.PAYMENT_STATUS = 5 ").append("\r\n"); //已繳款
//        sql.append(" AND P.GROUP_PK = ? ").append("\r\n"); // 帳單業者
//        args.add(groupPk);
//
//        if (null != channel)
//        {
//            sql.append(" AND W.CHANNEL = ? ").append("\r\n"); // 通路
//            args.add(channel);
//        }
//        else
//        {
//            sql.append(" AND W.CHANNEL in( ?, ?, ?, ?) ").append("\r\n"); // 通路
//            args.add(WriteOffPayment.CHANNEL_7_11);
//            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
//            args.add(WriteOffPayment.CHANNEL_HILIFE);
//            args.add(WriteOffPayment.CHANNEL_OK_MART);
//        }
//
//        // 資料期間 1: 銷帳日期  2:實際繳款日
//        if (new Integer(1).equals(qryDateType))
//            sql.append(" AND W.WRITE_OFF_DATE between ? and ? ").append("\r\n"); // 銷帳日期
//        else
//            sql.append(" AND W.TX_DATE between ? and ? ").append("\r\n"); // 實際繳款日
//
//        args.add(qryDateFrom);
//        args.add(qryDateTo);
//
//        sql.append(") TMP ").append("\r\n");
//        sql.append(" ").append("\r\n");
//        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ").append("\r\n");
//        sql.append("INNER  JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ").append("\r\n");
//        sql.append(") D ").append("\r\n");
//        sql.append("GROUP BY GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO ").append("\r\n");
//        //sql.append("ORDER BY GROUP_NAME, VIRTUAL_ACCOUNT_NO ").append("\r\n");
//
//        sql.append(" union all ");
//
//        // 再加上非帳單業者
//        sql.append("SELECT ");
//        sql.append(" GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO").append("\r\n");
//        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT ");
//        //非超商沒代收
//        sql.append(", SUM(AMOUNT  - BANK_AMOUNT ) AS REAL_AMOUNT ");
//        sql.append("FROM ");
//        sql.append("( ");
//        sql.append("SELECT TMP.* ");
//        sql.append(", C.COMMISSION_CODE_PK ");
//        sql.append(", C.COMMISSION_CODE_NAME ");
//        sql.append(", C.COMMISSION_TYPE ");
//        sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ");
//        sql.append(", ISNULL(C.BANK_AMOUNT, 0 ) AS BANK_AMOUNT ");
//        sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ");
//        sql.append(", ISNULL(GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ");
//        sql.append("FROM ");
//        sql.append("( ");
//        sql.append("select W.WRITE_OFF_PAYMENT_PK ");
//        sql.append(", P.PAYMENT_PK ");
//        sql.append(", P.GROUP_PK ").append("\r\n");
//        sql.append(", U.FORMAT_DEF_PK ");
//        sql.append(", W.AMOUNT ");
//        sql.append(", W.WRITE_OFF_DATE ");
//        sql.append(", W.CREATED_DATE ");
//        sql.append(", P.BARCODE_STORES_1 ");
//        sql.append(", P.TOTAL_AMOUNT ");
//        sql.append(", P.TOTAL_LOANABLE_AMOUNT ");
//        sql.append(", W.CHANNEL ");
//        sql.append(", RA.VIRTUAL_ACCOUNT_NO, RA.ACCOUNT_NO, AP.GROUP_NAME").append("\r\n");
//        sql.append(", SUBSTRING( P.BARCODE_STORES_1, 7, 9) AS COMMISSION_CODE ");
//        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ");
//        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ");
//        sql.append(" INNER JOIN APP_GROUP AP ON AP.GROUP_PK=P.GROUP_PK").append("\r\n");
//        sql.append(" LEFT  JOIN REAL_ACCOUNT RA ON RA.VIRTUAL_ACCOUNT_NO=SUBSTRING( P.VIRTUAL_ACCOUNT, 1, 4) and ra.GROUP_PK=p.GROUP_PK").append("\r\n");
//        sql.append(" where P.PAYMENT_STATUS = 5 "); //已繳款
//        sql.append(" AND P.GROUP_PK = ? "); // 帳單業者
//        args.add(groupPk);
//        
//        if (null != channel)
//        {
//            sql.append(" AND W.CHANNEL = ? "); // 通路
//            args.add(channel);
//        }
//        else
//        {
//            sql.append(" AND W.CHANNEL NOT IN ( ?, ?, ?, ?) "); // 通路
//            args.add(WriteOffPayment.CHANNEL_7_11);
//            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
//            args.add(WriteOffPayment.CHANNEL_HILIFE);
//            args.add(WriteOffPayment.CHANNEL_OK_MART);
//        }
//        
//        // 資料期間
//        // 資料期間 1: 銷帳日期  2:實際繳款日
//        if (new Integer(1).equals(qryDateType))
//            sql.append(" AND W.WRITE_OFF_DATE between ? and ? ").append("\r\n"); // 銷帳日期
//        else
//            sql.append(" AND W.TX_DATE between ? and ? ").append("\r\n"); // 實際繳款日
//        
//
//        args.add(qryDateFrom);
//        args.add(qryDateTo);
//        
//        sql.append(") TMP ");
//        sql.append(" ");
//        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ");
//        sql.append("INNER  JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ");
//        sql.append(") D ");
//        sql.append("GROUP BY GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO").append("\r\n");
//        //sql.append("ORDER BY GROUP_NAME, VIRTUAL_ACCOUNT_NO ").append("\r\n");
//        
//        sql.append(" ) ABC  ");
//        sql.append("GROUP BY GROUP_PK, GROUP_NAME, VIRTUAL_ACCOUNT_NO, ACCOUNT_NO ").append("\r\n");
//        sql.append("ORDER BY GROUP_NAME, VIRTUAL_ACCOUNT_NO ").append("\r\n");
//
//
//        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
//
//    }
    
}
